﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace Qzb.DBHelper.ADO
{
    internal class MsSqlHelper : IDbHelper
    {
        #region "私有成员"

        private SqlConnection mConnection;
        private SqlTransaction mTransaction;

        #endregion

        #region "构造函数"

        public MsSqlHelper(string connectionString)
        {
            mConnection = new SqlConnection(connectionString);
            ConnectionIsOpen = false;
            TransactionState = TransactionType.None;
        }

        public MsSqlHelper(SqlConnection connection)
        {
            mConnection = connection;
            ConnectionIsOpen = false;
            TransactionState = TransactionType.None;
        }

        #endregion

        #region "IDbCommandHelp成员"

        public string SqlPlaceHolder
        {
            get { return "@"; }
        }

        public IDataParameter CreateParameter()
        {
            return new SqlParameter();
        }

        public IDataParameter CreateParameter(string parameterName, object value)
        {
            return new SqlParameter(parameterName, value);
        }

        public IDataParameter CreateParameter(string parameterName, Type dataType)
        {
            return new SqlParameter(parameterName, GetSqlDbType(dataType));
        }

        public IDataParameter CreateParameter(string parameterName, ColumnType dataType)
        {
            return new SqlParameter(parameterName, GetSqlDbType(dataType));
        }

        public IDataParameter CreateParameter(string parameterName, object value, ParameterDirection direction)
        {
            return new SqlParameter
            {
                ParameterName = parameterName,
                Value = value,
                Direction = direction
            };
        }

        public IDataParameter CreateParameter(string parameterName, object value, ColumnType dataType)
        {
            var parameter = CreateParameter(parameterName, dataType);
            parameter.Value = value;
            return parameter;
        }

        public IDataParameter CreateParameter(string parameterName, ColumnType dataType, int size)
        {
            return new SqlParameter(parameterName, GetSqlDbType(dataType), size);
        }

        public IDataParameter CreateParameter(string parameterName, object value, ColumnType dataType, int size)
        {
            var parameter = CreateParameter(parameterName, dataType, size);
            parameter.Value = value;
            return parameter;
        }

        public IDbCommand CreateDeleteCommand(string sourceTableName, params IDataParameter[] conditionParameters)
        {
            var condition = SqlBuilder.CreateWhereClause(conditionParameters, SqlPlaceHolder);
            var commandText = SqlBuilder.CreateDeleteSql(sourceTableName, condition);

            var command = CreateDbCommand(commandText, CommandType.Text, new IDataParameter[0]);
            foreach (var parameter in conditionParameters)
            {
                command.Parameters.Add(parameter);
            }

            return command;
        }

        public IDbCommand CreateInsertCommand(string sourceTableName, params IDataParameter[] parameters)
        {
            var commandText = SqlBuilder.CreateInsertSql(sourceTableName, GetColumns(parameters), SqlPlaceHolder);
            return CreateDbCommand(commandText, CommandType.Text, parameters);
        }

        public IDbCommand CreateUpdateCommand(string sourceTableName, IDataParameter[] whereParams, params IDataParameter[] cmdParams)
        {
            var where = SqlBuilder.CreateWhereClause(whereParams, SqlPlaceHolder);
            var command = (SqlCommand)CreateUpdateCommand(sourceTableName, where, cmdParams);
            foreach (var parameter in whereParams)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }

        public IDbCommand CreateUpdateCommand(string sourceTableName, string where, params IDataParameter[] cmdParams)
        {
            var commandText = SqlBuilder.CreateUpdateSql(sourceTableName, GetColumns(cmdParams), where, SqlPlaceHolder);
            return CreateDbCommand(commandText, CommandType.Text, cmdParams);
        }

        #endregion

        #region "IDbHelp成员"

        public bool ConnectionIsOpen { get; private set; }

        public TransactionType TransactionState { get; private set; }

        public void OpenConnection()
        {
            if (TransactionState != TransactionType.Open)
            {
                if (mConnection.State == ConnectionState.Closed)
                {
                    try
                    {
                        mConnection.Open();
                        ConnectionIsOpen = true;
                    }
                    catch (Exception exception)
                    {
                        ConnectionIsOpen = false;
                        throw exception;
                    }
                }
            }
        }

        public void CloseConnection()
        {
            if (TransactionState != TransactionType.Open)
            {
                if (mConnection.State != ConnectionState.Closed)
                {
                    try
                    {
                        mConnection.Close();
                        ConnectionIsOpen = false;
                    }
                    catch (Exception exception)
                    {
                        throw exception;
                    }
                }
            }
        }

        public void BeginTransaction()
        {
            OpenConnection();
            try
            {
                mTransaction = mConnection.BeginTransaction();
                TransactionState = TransactionType.Open;
            }
            catch (Exception exception)
            {
                CloseConnection();
                throw exception;
            }
        }

        public void RollbackTransaction()
        {
            if (mTransaction != null)
            {
                try
                {
                    mTransaction.Rollback();
                    mTransaction = null;
                    TransactionState = TransactionType.Rollback;
                }
                finally
                {
                    CloseConnection();
                }
            }
        }

        public void CommitTransaction()
        {
            if (mTransaction != null && mTransaction.Connection != null)
            {
                try
                {
                    mTransaction.Commit();
                    mTransaction = null;
                    TransactionState = TransactionType.Commit;
                }
                finally
                {
                    CloseConnection();
                }
            }
        }

        public bool IsTableExists(string tableName)
        {
            if (string.IsNullOrEmpty(tableName))
                return false;

            try
            {
                var ret = ExecuteScalar($"select top 1 1 from sysObjects where Id=OBJECT_ID(N'{tableName}') and xtype='U'");
                return ret != null && ret?.ToString() == "1" ? true : false;
            }
            catch
            {
                return false;
            }
        }

        public long GetCurrentIdentity(string tableName)
        {
            if (string.IsNullOrEmpty(tableName))
                return 0;

            try
            {
                var id = ExecuteScalar("select IDENT_CURRENT('" + tableName + "')");
                return (id == null) ? 0 : Convert.ToInt32(id);
            }
            catch
            {
                return 0;
            }
        }

        public int ExecuteNonQuery(IDbCommand cmd)
        {
            int num;
            try
            {
                OpenConnection();
                num = cmd.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(
                    (exception.Message + SqlBuilder.GetFormatedSql(cmd, SqlPlaceHolder))
                    , exception);
            }
            finally
            {
                CloseConnection();
            }
            return num;
        }

        public int ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(commandText, CommandType.Text, null);
        }

        public int ExecuteNonQuery(string commandText, CommandType cmdType, params IDataParameter[] cmdParams)
        {
            using (var command = CreateDbCommand(commandText, cmdType, cmdParams))
            {
                var num = ExecuteNonQuery(command);
                for (var i = 0; i < command.Parameters.Count; i++)
                {
                    if (!((command.Parameters[i].Direction == ParameterDirection.Input)
                        || (cmdParams[i] is SqlParameter)))
                    {
                        cmdParams[i].Value = command.Parameters[i].Value;
                    }
                }
                return num;
            }
        }

        public int InsertDataTable(DataTable dataTable, string tableName)
        {
            var isTran = true;

            if (mTransaction == null)
            {
                isTran = false;
                BeginTransaction();
            }

            try
            {
                using (var bulkCopy = new SqlBulkCopy(mConnection, SqlBulkCopyOptions.KeepIdentity, mTransaction))
                {
                    //一次批量的插入的数据量
                    bulkCopy.BatchSize = 1000;

                    //超时之前操作完成所允许的秒数，如果超时则事务不会提交 ，数据将回滚，所有已复制的行都会从目标表中移除
                    bulkCopy.BulkCopyTimeout = 60;

                    //設定 NotifyAfter 属性，以便在每插入10000 条数据时，呼叫相应事件。 
                    bulkCopy.NotifyAfter = 10000;

                    //设置要批量写入的表
                    bulkCopy.DestinationTableName = tableName;

                    //自定义的datatable和数据库的字段进行对应
                    for (int i = 0; i < dataTable.Columns.Count; i++)
                    {
                        bulkCopy.ColumnMappings.Add(dataTable.Columns[i].ColumnName.ToString(), dataTable.Columns[i].ColumnName.ToString());
                    }

                    //批量写入
                    bulkCopy.WriteToServer(dataTable);
                }

                if (!isTran)
                    CommitTransaction();

                return dataTable.Rows.Count;
            }
            catch (Exception ex)
            {
                if (!isTran)
                    RollbackTransaction();
                throw ex;
            }
        }

        public object ExecuteScalar(string commandText)
        {
            return ExecuteScalar(commandText, CommandType.Text, null);
        }

        public object ExecuteScalar(string commandText, CommandType cmdType, params IDataParameter[] cmdParams)
        {
            object value;
            using (var command = CreateDbCommand(commandText, cmdType, cmdParams))
            {
                try
                {
                    OpenConnection();
                    value = command.ExecuteScalar();
                }
                catch (Exception exception)
                {
                    throw new Exception(
                        exception.Message + SqlBuilder.GetFormatedSql(command, SqlPlaceHolder),
                        exception);
                }
                finally
                {
                    CloseConnection();
                }
            }
            return value;
        }

        public IDataReader GetDataReader(string selectSql)
        {
            return GetDataReader(selectSql, CommandType.Text, null);
        }

        public IDataReader GetDataReader(string commandText, CommandType cmdType, params IDataParameter[] cmdParams)
        {
            IDataReader reader;
            var cmd = CreateDbCommand(commandText, cmdType, cmdParams);
            try
            {
                OpenConnection();
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exception)
            {
                CloseConnection();
                throw new Exception(
                    (exception.Message + SqlBuilder.GetFormatedSql(cmd, SqlPlaceHolder)),
                    exception);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
            }
            return reader;
        }

        public DataSet GetDataSet(string selectSql)
        {
            return GetDataSet(selectSql, CommandType.Text, null);
        }

        public DataSet GetDataSet(string commandText, CommandType cmdType, params IDataParameter[] cmdParams)
        {
            var dataSet = new DataSet();
            using (var adapter = new SqlDataAdapter())
            {
                try
                {
                    adapter.SelectCommand = CreateDbCommand(commandText, cmdType, cmdParams);
                    adapter.Fill(dataSet, SqlBuilder.GetTableNameFromSQL(commandText));
                }
                catch (Exception exception)
                {
                    if (adapter.SelectCommand == null)
                    {
                        throw new Exception(
                            (exception.Message + commandText),
                            exception);
                    }
                    throw new Exception(
                        (exception.Message + SqlBuilder.GetFormatedSql(adapter.SelectCommand, SqlPlaceHolder)),
                        exception);
                }
                finally
                {
                    adapter.TableMappings.Clear();
                }
            }
            return dataSet;
        }

        public DataTable GetDataTable(string selectSql)
        {
            return GetDataTable(selectSql, CommandType.Text, null);
        }

        public DataTable GetDataTable(string commandText, CommandType cmdType, params IDataParameter[] cmdParams)
        {
            return GetDataSet(commandText, cmdType, cmdParams).Tables[0];
        }

        public DataView GetDataView(string selectSql)
        {
            return GetDataView(selectSql, CommandType.Text, null);
        }

        public DataView GetDataView(string commandText, CommandType cmdType, params IDataParameter[] cmdParams)
        {
            return GetDataTable(commandText, cmdType, cmdParams).DefaultView;
        }

        #endregion

        #region "私有函数"

        private SqlDbType GetSqlDbType(Type dataType)
        {
            switch (dataType.ToString())
            {
                case "System.String":
                    return SqlDbType.VarChar;
                case "System.Decimal":
                    return SqlDbType.Decimal;
                case "System.DateTime":
                    return SqlDbType.DateTime;
                case "System.Byte[]":
                    return SqlDbType.Binary;
            }
            return SqlDbType.VarChar;
        }

        private SqlDbType GetSqlDbType(ColumnType dataType)
        {
            switch (dataType)
            {
                case ColumnType.Decimal:
                    return SqlDbType.Decimal;
                case ColumnType.Binary:
                    return SqlDbType.Binary;
                case ColumnType.VarBinary:
                    return SqlDbType.VarBinary;
                case ColumnType.Date:
                    return SqlDbType.Date;
                case ColumnType.DateTime:
                    return SqlDbType.DateTime;
                case ColumnType.Char:
                    return SqlDbType.Char;
                case ColumnType.VarChar:
                    return SqlDbType.VarChar;
                case ColumnType.Text:
                    return SqlDbType.Text;
                case ColumnType.Blob:
                    return SqlDbType.Image;
            }
            return SqlDbType.VarChar;
        }

        private SqlDbType GetSqlDbType(DbType dataType)
        {
            switch (dataType)
            {
                case DbType.AnsiString:
                    return SqlDbType.VarChar;
                case DbType.Binary:
                    return SqlDbType.Binary;
                case DbType.Date:
                    return SqlDbType.Date;
                case DbType.DateTime:
                    return SqlDbType.DateTime;
                case DbType.String:
                    return SqlDbType.VarChar;
                case DbType.VarNumeric:
                    return SqlDbType.Decimal;
                case DbType.AnsiStringFixedLength:
                    return SqlDbType.Char;
                case DbType.StringFixedLength:
                    return SqlDbType.Char;
            }
            return SqlDbType.VarChar;
        }

        private SqlCommand CreateDbCommand(string commandText, CommandType cmdType, params IDataParameter[] parameters)
        {
            var command = new SqlCommand()
            {
                CommandType = cmdType,
                Connection = mConnection,
                CommandText = commandText,
                CommandTimeout = 300
            };

            if (mTransaction != null)
                command.Transaction = mTransaction;

            if (parameters != null)
            {
                if (command.CommandText.IndexOf("?") > 0)
                {
                    var regex = new Regex(@"\?");
                    foreach (var parameter in parameters)
                    {
                        command.CommandText = regex.Replace(
                            command.CommandText
                            , SqlBuilder.GetSqlParameterName(parameter.ParameterName, SqlPlaceHolder)
                            , 1);

                        var param = ConvertToSqlParameter(parameter);
                        if (param.Value == null)
                        {
                            param.IsNullable = true;
                            param.Value = DBNull.Value;
                        }

                        command.Parameters.Add(param);
                    }
                }
                else
                {
                    foreach (var parameter in parameters)
                    {
                        var param = ConvertToSqlParameter(parameter);
                        if (param.Value == null)
                        {
                            param.IsNullable = true;
                            param.Value = DBNull.Value;
                        }

                        command.Parameters.Add(param);
                    }
                }
            }

            return command;
        }

        private SqlParameter ConvertToSqlParameter(IDataParameter param)
        {
            if (param is SqlParameter)
                return (SqlParameter)param;

            var parameter = new SqlParameter(param.ParameterName, param.Value)
            {
                Direction = param.Direction,
                DbType = param.DbType
            };

            if (parameter.Direction != ParameterDirection.Input)
                parameter.SqlDbType = GetSqlDbType(param.DbType);

            // if (param is OleDbParameter)
            //     parameter.Size = (param as OleDbParameter).Size;

            return parameter;
        }

        private string[] GetColumns(params IDataParameter[] parameters)
        {
            var list = new ArrayList();
            foreach (var parameter in parameters)
            {
                list.Add(parameter.ParameterName);
            }
            return (string[])list.ToArray(typeof(string));
        }

        #endregion
    }
}
